﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; 
namespace LOGIN
{
    public partial class UseTable : Form
    {
        public UseTable()
        {
            InitializeComponent();
            this.dgv_outpuetable.AllowUserToAddRows = false;

        }
        private DataTable OutputTable;

        private void OutputTable_Load(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=(local);Database=MyHospital;Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "SELECT BNo,c.YKName,Lydw,b.DrugName,Lyl,b.IndPrice,Lydate,State,dbo.fn_GetPy(DrugName) AS pymc FROM dbo.medicineBuy cs JOIN dbo.medicineWareHouse c ON cs.YKNo = c.YKNo JOIN dbo.medicineData b ON cs.MedicineID=b.MedicineID where State=0;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            this.OutputTable = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(OutputTable);
            sqlConnection.Close();
            this.dgv_outpuetable.DataSource = OutputTable;
            dgv_outpuetable.Columns[0].ReadOnly = true;
            dgv_outpuetable.Columns[1].ReadOnly = true;
            dgv_outpuetable.Columns["pymc"].Visible = false;
            dgv_outpuetable.Columns["BNo"].HeaderText = "ID";
            dgv_outpuetable.Columns["YKName"].HeaderText = "药库";
            dgv_outpuetable.Columns["Lydw"].HeaderText = "领药单位";
            dgv_outpuetable.Columns["DrugName"].HeaderText = "药品名";
            dgv_outpuetable.Columns["Lyl"].HeaderText = "领药数量";
            dgv_outpuetable.Columns["IndPrice"].HeaderText = "单价";
            dgv_outpuetable.Columns["Lydate"].HeaderText = "领药时间";
            dgv_outpuetable.Columns["State"].HeaderText = "是否调拨";
            
        }
        

        private void dgv_outpuetable_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dgv_outpuetable.SelectedCells.Count != 0)
            {
                int index = dgv_outpuetable.CurrentRow.Index;
                this.lydw.Text = dgv_outpuetable.Rows[index].Cells[2].Value.ToString();
                this.lydate.Text = dgv_outpuetable.Rows[index].Cells[6].Value.ToString();
                this.label5.Text = dgv_outpuetable.Rows[index].Cells[3].Value.ToString();
                

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=(local);Database=MyHospital;Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            if (this.comboBox1.SelectedIndex == 0 )
            {
                sqlCommand.CommandText = "SELECT BNo,c.YKName,Lydw,b.DrugName,Lyl,b.IndPrice,Lydate FROM dbo.medicineBuy cs JOIN dbo.medicineWareHouse c ON cs.YKNo = c.YKNo JOIN dbo.medicineData b ON cs.MedicineID=b.MedicineID WHERE c.YKNo='1';";
                
            }
            else if (this.comboBox1.SelectedIndex == 1 )
            {
                sqlCommand.CommandText = "SELECT BNo,c.YKName,Lydw,b.DrugName,Lyl,b.IndPrice,Lydate FROM dbo.medicineBuy cs JOIN dbo.medicineWareHouse c ON cs.YKNo = c.YKNo JOIN dbo.medicineData b ON cs.MedicineID=b.MedicineID WHERE c.YKNo='2';";
                
            }
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            DataTable outputTable = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(outputTable);
            sqlConnection.Close();
            this.dgv_outpuetable.DataSource = outputTable;
            dgv_outpuetable.Columns[0].ReadOnly = true;
            dgv_outpuetable.Columns[1].ReadOnly = true;
        }

        private void button2_Click(object sender, EventArgs e)
        {

            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=(local);Database=MyHospital;Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            SqlCommand sqlCommand1 = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand1.Connection = sqlConnection;
            sqlCommand.CommandText = "select MedicineSum from medicineData WHERE DrugName=@DrugName; ";
            sqlCommand1.CommandText = "select Lyl from medicineBuy c JOIN dbo.medicineData cs ON c.MedicineID = cs.MedicineID WHERE DrugName=@DrugName; ";
            sqlCommand.Parameters.Add("@DrugName",this.label5.Text.ToString());
            sqlCommand1.Parameters.Add("@DrugName", this.label5.Text.ToString()); 

            
             sqlConnection.Open(); 
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            int m = 0, n = 0;
            if (sqlDataReader.Read())
            {
                m = Convert.ToInt32(sqlDataReader["MedicineSum"].ToString());
            }
           
            sqlDataReader.Close();

            SqlDataReader sqlDataReader1 = sqlCommand1.ExecuteReader();
            if (sqlDataReader1.Read())
            {
                n = Convert.ToInt32(sqlDataReader1["Lyl"].ToString());
            }
           
            sqlDataReader1.Close(); 

            int a = m-n;

            SqlCommand updateCommand = new SqlCommand();
            updateCommand.Connection = sqlConnection;
            updateCommand.CommandText =
                "UPDATE medicineData"
                + " SET MedicineSum=@MedicineSum"
                + " WHERE DrugName=@DrugName;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            if (a > 0) 
            {

                updateCommand.Parameters.AddWithValue("@DrugName", this.label5.Text);
                updateCommand.Parameters.AddWithValue("@MedicineSum", Convert.ToString(a));
                SqlCommand updateCommand1 = new SqlCommand();
                updateCommand1.Connection = sqlConnection;
                updateCommand1.CommandText =
                    "UPDATE medicineBuy SET State=1 WHERE BNo=@BNo;";
                updateCommand1.Parameters.Add("@BNo", SqlDbType.Char, 10, "BNo");
                sqlDataAdapter.UpdateCommand = updateCommand1;
                DataTable outputTable1 = (DataTable)this.dgv_outpuetable.DataSource;
                int rowAffected = sqlDataAdapter.Update(outputTable1);
                MessageBox.Show("出库成功，剩余"+a+"库存量");
                MessageBox.Show("更新" + rowAffected.ToString() + "行。");
                int rowAffected1 = updateCommand.ExecuteNonQuery();                                                 
                sqlConnection.Close();
                MessageBox.Show("药品库存量更新" + rowAffected1.ToString() + "行。");  


            }
            else if (a == 0) 
            {

                updateCommand.Parameters.AddWithValue("@DrugName", this.label5.Text);
                updateCommand.Parameters.AddWithValue("@MedicineSum", Convert.ToString(a));
                SqlCommand updateCommand1 = new SqlCommand();
                updateCommand1.Connection = sqlConnection;
                updateCommand1.CommandText =
                    "UPDATE medicineBuy SET State=1 WHERE BNo=@DBNo;";
                updateCommand1.Parameters.Add("@BNo",SqlDbType.Char,10,"BNo");
                //SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.UpdateCommand = updateCommand1;
                DataTable outputTable1 = (DataTable)this.dgv_outpuetable.DataSource;

                int rowAffected = sqlDataAdapter.Update(outputTable1);
                //int rowAffected = updateCommand1.ExecuteNonQuery();
                MessageBox.Show("出库成功,库存量为0，请及时补货");
                MessageBox.Show("更新" + rowAffected.ToString() + "行。");
                int rowAffected1 = updateCommand.ExecuteNonQuery();                                                
                sqlConnection.Close();
                MessageBox.Show("药品库存量更新" + rowAffected1.ToString() + "行。");  
            }
            else if (a < 0) 
            {
               
                sqlConnection.Close();
                MessageBox.Show("库存量不足，无法调拨");
            }
            
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            DataRow[] searchResultRows =
                this.OutputTable.Select("pymc LIKE '%" + this.textBox1.Text.Trim() + "%'");         
            DataTable searchResultTable = this.OutputTable.Clone();                                     
            foreach (DataRow row in searchResultRows)                                                     
            {
                searchResultTable.ImportRow(row);                                                       
            }
            this.dgv_outpuetable.DataSource = searchResultTable;    
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

       
    }
}
